package com.fh.service.impl;


import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fh.bean.*;
import com.fh.mapper.CourseMapper;
import com.fh.mapper.ScoreMapper;
import com.fh.mapper.StudentMapper;
import com.fh.mapper.TeacherMapper;
import com.fh.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Administrator
 * @description 针对表【student】的数据库操作Service实现
 * @createDate 2024-03-22 11:02:13
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper,Student> implements StudentService {

    @Autowired
    private CourseMapper courseMapper;

    @Autowired
    private StudentMapper studentMapper;

    @Autowired
    private ScoreMapper scoreMapper;

    @Autowired
    private TeacherMapper teacherMapper;

    /**
     * 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
     *
     * @return
     */
    @Override
    public List<Student> queryCourseGrade() {
        /*
        获取所有的课程与成绩信息
         */
        List<Score> scores = scoreMapper.selectList(null);
        ArrayList<String> studentID = new ArrayList<>();
        /*
        通过Stream流得到一个Map
        * */
        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                /*遍历Map*/.forEach((k, v) -> {
                    //                    使用cid排序
                    v.sort(Comparator.comparingInt(Score::getcId));
                    if(v.get(0)
                            .getsScore() > v.get(1)
                            .getsScore()) {
                        studentID.add(String.valueOf(v.get(0)
                                .getsId()));
                    }
                });
        return listByIds(studentID);
    }

    /**
     * 查询成绩01课程小于02课程接口
     *
     * @return
     */
    @Override
    public List<Student> queryCourseGradeSmall() {
        //        获取所有课程信息
        List<Score> scores = scoreMapper.selectList(null);
        ArrayList<String> studentID = new ArrayList<>();
        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                .forEach((k, v) -> {
                    v.sort(Comparator.comparingInt(Score::getcId));
                    if(v.get(0)
                            .getsScore() < v.get(1)
                            .getsScore()) {
                        studentID.add(String.valueOf(v.get(0)
                                .getsId()));
                    }
                });
        return listByIds(studentID);
    }

    /**
     * 查询平均成绩大于60
     *
     * @return
     */
    @Override
    public List<StudentAvgGradeBean> queryAvgGradeBigSixty() {
        //        查询所有课程信息
        List<Score> scores = scoreMapper.selectList(null);
        //        存放 大于60的成绩与ID
        HashMap<Integer,Double> ids = new HashMap<>();
        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                .forEach((k, v) -> {
                    double v1 = v.stream()
                            .mapToDouble(Score::getsScore)
                            .average()
                            .orElse(0.0);
                    if(v1 >= 60) {
                        ids.put(k, v1);
                    }
                });
        List<Student> students = listByIds(ids.keySet());
        ArrayList<StudentAvgGradeBean> studentAvgGradeBigSixties = new ArrayList<>();
        students.stream()
                .collect(Collectors.groupingBy(Student::getSId))
                .forEach((k, v) -> {
                    studentAvgGradeBigSixties.add(new StudentAvgGradeBean(v.get(0)
                            .getSId(), v.get(0)
                            .getsName(), ids.get(k)));
                });


        return studentAvgGradeBigSixties;
    }

    /**
     * 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
     */
    @Override
    public List<StudentAvgGradeBean> queryAvgGradeSmallSixty() {
        List<Score> scores = scoreMapper.selectList(null);
        //        存放 大于60的成绩与ID
        HashMap<Integer,Double> avgGrade = new HashMap<>();
        ArrayList<Integer> ids = new ArrayList<>();
        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                .forEach((k, v) -> {
                    double v1 = v.stream()
                            .mapToDouble(Score::getsScore)
                            .average()
                            .orElse(0.0);
                    System.out.println("v1: " + v1);
                    if(v1 > 60) {
                        ids.add(k);
                        return;
                    }
                    avgGrade.put(k, v1);
                });
        List<Student> students = studentMapper.selectBatchNotIds(ids);

        ArrayList<StudentAvgGradeBean> studentAvgGradeBigSixties = new ArrayList<>();
        students.stream()
                .collect(Collectors.groupingBy(Student::getSId))
                .forEach((k, v) -> {
                    studentAvgGradeBigSixties.add(new StudentAvgGradeBean(v.get(0)
                            .getSId(), v.get(0)
                            .getsName(), avgGrade.get(k)));
                });


        return studentAvgGradeBigSixties;
    }

    /**
     * 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
     */
    @Override
    public List<StudentGradeCourseBean> queryStudentSumGrade() {
        List<Student> students = list();

        List<Score> scores = scoreMapper.selectList(null);

        //        防止两张表的数据对不上 所以 以学生表为准
        List<StudentGradeCourseBean> studentGradeCourseBeans = new ArrayList<>(students.stream()
                .map(student -> {
                    StudentGradeCourseBean studentGradeCourseBean = new StudentGradeCourseBean();
                    studentGradeCourseBean.setSId(student.getSId());
                    studentGradeCourseBean.setSName(student.getsName());
                    // 可以继续设置其他属性
                    return studentGradeCourseBean;
                })
                .toList());
        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                .forEach((s, k) -> {
                    //                    课程成绩
                    studentGradeCourseBeans.get(s - 1)
                            .setSumScore(k.stream()
                                    .mapToDouble(Score::getsScore)
                                    .sum());
                    studentGradeCourseBeans.get(s - 1)
                            .setCntCourse(k.size());
                });

        return studentGradeCourseBeans.stream()
                .peek(s -> {
                    if(ObjectUtil.isNull(s.getSumScore())) {
                        s.setSumScore(0.0);
                    }
                    if(ObjectUtil.isNull(s.getCntCourse())) {
                        s.setCntCourse(0);
                    }
                })
                .toList();
    }

    /**
     * 6、查询"李"姓老师的数量
     */
    @Override
    public Long queryLiCount() {
        List<Teacher> teachers = teacherMapper.selectList(null);
        return teachers.stream()
                .filter(s -> {
                    return s.gettName()
                            .startsWith("李");
                })
                .count();
    }

    /**
     * 询学过"张三"老师授课的同学的信息
     */
    @Override
    public List<Student> queryStudyZsStudent() {
        List<Teacher> teachers = teacherMapper.selectList(null);

        Map<Integer,List<Teacher>> teachersZsIDs = teachers.stream()
                .filter(s -> {
                    return s.gettName()
                            .equals("张三");
                })
                .collect(Collectors.groupingBy(Teacher::gettId));


        List<Course> courses = courseMapper.selectList(null);
        Map<Integer,List<Course>> corsesCIDs = courses.stream()
                .filter(key -> {
                    return ObjectUtil.isNotNull(teachersZsIDs.get(key.gettId()));
                })
                .collect(Collectors.groupingBy(Course::getcId));


        List<Score> scores = scoreMapper.selectList(null);
        return listByIds(scores.stream()
                .filter(s -> {
                    return ObjectUtil.isNotNull(corsesCIDs.get(s.getcId()));
                })
                .map(Score::getsId)
                .toList());
    }

    /**
     * 查询没学过"张三"老师授课的同学的信息
     */
    @Override
    public List<Student> queryNotStudyZsStudent() {
        List<Teacher> teachers = teacherMapper.selectList(null);

        Map<Integer,List<Teacher>> teachersZsIDs = teachers.stream()
                .filter(s -> {
                    return s.gettName()
                            .equals("张三");
                })
                .collect(Collectors.groupingBy(Teacher::gettId));


        List<Course> courses = courseMapper.selectList(null);
        Map<Integer,List<Course>> corsesCIDs = courses.stream()
                .filter(key -> {
                    return ObjectUtil.isNotNull(teachersZsIDs.get(key.gettId()));
                })
                .collect(Collectors.groupingBy(Course::getcId));

        List<Score> scores = scoreMapper.selectList(null);
        return studentMapper.selectBatchNotIds(scores.stream()
                .filter(s -> {
                    return ObjectUtil.isNotNull(corsesCIDs.get(s.getcId()));
                })
                .map(Score::getsId)
                .toList());
    }

    /**
     * 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
     */
    @Override
    public List<Student> queryStudyZeroOneAndTwo() {
        List<Score> scores = scoreMapper.selectList(null);

        ArrayList<Integer> ids = new ArrayList<>();

        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                .forEach((s, k) -> {
                    k.sort(Comparator.comparingInt(Score::getcId));
                    if(k.get(0)
                            .getcId()
                            .equals(1) && k.get(1)
                            .getcId()
                            .equals(2)) {
                        ids.add(s);
                    }
                });

        return listByIds(ids);
    }

    /**
     * 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
     *
     * @return
     */
    @Override
    public List<Student> queryStudyZeroOneAndNotTwo() {
        List<Score> scores = scoreMapper.selectList(null);

        ArrayList<Integer> ids = new ArrayList<>();

        scores.stream()
                .collect(Collectors.groupingBy(Score::getsId))
                .forEach((s, k) -> {
                    k.sort(Comparator.comparingInt(Score::getcId));
                    if(k.get(0)
                            .getcId()
                            .equals(1) && !(k.get(1)
                            .getcId()
                            .equals(2))) {
                        ids.add(s);
                    }
                });
        return listByIds(ids);
    }


    /**
     * 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
     */
    @Override
    public List<Student> queryZeroOneDiscipline() {
        List<Score> scores = scoreMapper.selectList(null);

        Map<Integer,List<Score>> studentIDs = scores.stream()
                .collect(Collectors.groupingBy(Score::getsId));

        List<Integer> studentIdIsOne = new ArrayList<>(studentIDs.get(1)
                .stream()
                .map(Score::getcId)
                .sorted(Comparator.naturalOrder())
                .toList());
        Collections.sort(studentIdIsOne);

        ArrayList<Integer> ids = new ArrayList<>();
        studentIDs.remove(1);
        studentIDs.forEach((k, v) -> {

            if(CollUtil.isEqualList(v.stream()
                    .map(Score::getcId)
                    .sorted(Comparator.naturalOrder())
                    .toList(), studentIdIsOne)) {
                ids.add(k);
            }
        });
        return listByIds(ids);
    }

    /**
     * 查询没学过"张三"老师讲授的任一门课程的学生姓名
     */
    @Override
    public List<String> queryNotStudyZs() {
        //             获取 老师名字为张三的ID
             /*   List<Integer> teacherIDs = teacherMapper.selectList(new QueryWrapper<Teacher>().eq("t_name", "张三"))
                .stream()
                .map(Teacher::gettId)
                .toList();
        List<Integer> coursesIDs = courseMapper.selectList(new QueryWrapper<Course>().in("t_id", teacherIDs))
                .stream()
                .map(Course::getcId)
                .toList();

        List<Integer> scoreIDs = scoreMapper.selectList(new QueryWrapper<Score>().in("c_id", coursesIDs))
                .stream()
                .map(Score::getsId)
                .toList();

        return studentMapper.selectBatchNotIds(scoreIDs)
                .stream()
                .map(Student::getsName)
                .toList();*/

        //        TODO 我的屎山 由于不通俗易懂 上面写了一个 容易理解的使用todo的原因是看着比较亮 哈哈哈哈
        return studentMapper.selectBatchNotIds(scoreMapper.selectList(new QueryWrapper<Score>().in("c_id", courseMapper.selectList(new QueryWrapper<Course>().in("t_id", teacherMapper.selectList(new QueryWrapper<Teacher>().eq("t_name", "张三"))
                                        .stream()
                                        .map(Teacher::gettId)
                                        .toList()))
                                .stream()
                                .map(Course::getcId)
                                .toList()))
                        .stream()
                        .map(Score::getsId)
                        .toList())
                .stream()
                .map(Student::getsName)
                .toList();
    }

    /**
     * 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
     */
    @Override
    public List<StudentAvgGradeBean> queryNotTwoPass() {

        List<Score> scores = scoreMapper.selectList(null);
        Map<Integer,List<Score>> scoresGroups = scores.stream()
                .collect(Collectors.groupingBy(Score::getsId));

        HashMap<Integer,Double> grade = new HashMap<>();
        ArrayList<Integer> ids = new ArrayList<>();
        scoresGroups.forEach((k, v) -> {
            if(v.stream()
                    .mapToDouble(Score::getsScore)
                    .filter(s -> s < 60)
                    .count() < 2) {
                ids.add(k);
                return;
            }
            grade.put(k, v.stream()
                    .mapToDouble(Score::getsScore)
                    .average()
                    .orElse(0.0));
        });

        ArrayList<StudentAvgGradeBean> gradeBeans = new ArrayList<>();
        studentMapper.selectList(new QueryWrapper<Student>().notIn("s_id", ids))
                .forEach(s -> {
                    StudentAvgGradeBean studentAvgGradeBean = new StudentAvgGradeBean();
                    studentAvgGradeBean.setsId(s.getSId());
                    studentAvgGradeBean.setsName(s.getsName());
                    gradeBeans.add(studentAvgGradeBean);
                });
        return gradeBeans.stream()
                .peek(s -> {
                    if(ObjectUtil.isNull(s.getAvgScore()) && ObjectUtil.isNull(grade.get(s.getsId()))) {
                        s.setAvgScore(0.0);
                        return;
                    }
                    s.setAvgScore(grade.get(s.getsId()));
                })
                .toList();
    }

    /**
     * 检索"01"课程分数小于60，按分数降序排列的学生信息
     *
     * @return
     */
    @Override
    public List<GradePass> queryGradePassSixty() {
        List<Score> scores = scoreMapper.selectList(new QueryWrapper<Score>().eq("c_id", 1));
        Map<Integer,List<Score>> idsMap = scores.stream()
                .filter(s -> {
                    return s.getsScore() < 60;
                })
                .collect(Collectors.groupingBy(Score::getsId));
        List<Student> students = studentMapper.selectList(new QueryWrapper<Student>().notIn("s_id", idsMap.keySet()));

        System.out.println("数据: " + idsMap);
        List<GradePass> gradePasses = students.stream()
                .map(s -> {
                    GradePass gradePass = new GradePass();
                    BeanUtil.copyProperties(s, gradePass);
                    return gradePass;
                })
                .toList();

        return gradePasses.stream()
                .peek(s -> {
                    if(ObjectUtil.isNull(idsMap.get(s.getSId()))) {
                        s.setScore(0.0);
                        return;
                    }
                    s.setScore(Double.valueOf(idsMap.get(s.getSId())
                            .get(0)
                            .getsScore()));
                })
                .toList();
    }

    /**
     * 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
     */
    @Override
    public List<GradeCourse> queryAvgMark() {
        List<Student> students = studentMapper.selectList(null);
        List<Score> scores = scoreMapper.selectList(null);

        Map<Integer,List<Score>> scoresIdsMap = scores.stream()
                .sorted(Comparator.comparingInt(Score::getcId))
                .collect(Collectors.groupingBy(Score::getsId));




        return students.stream()
                .map(s -> {
                    GradeCourse gradeCourse = new GradeCourse();
                    gradeCourse.setSId(s.getSId());
                    gradeCourse.setSName(s.getsName());

                    if(ObjectUtil.isNotNull(scoresIdsMap.get(s.getSId()))) {
                        gradeCourse.setAvgGrade(Double.parseDouble(String.format("%.2f", scoresIdsMap.get(s.getSId())
                                .stream()
                                .mapToDouble(Score::getsScore)
                                .average()
                                .orElse(0.0))));
                    } else {
                        gradeCourse.setAvgGrade(0.0);
                    }
                    if(ObjectUtil.isNotNull(scoresIdsMap.get(s.getSId())) && scoresIdsMap.get(s.getSId())
                            .size() >= 1) {
                        gradeCourse.setLanguage(Double.valueOf(scoresIdsMap.get(s.getSId())
                                .get(0)
                                .getsScore()));
                    } else {
                        gradeCourse.setLanguage(0.0);
                    }
                    if(ObjectUtil.isNotNull(scoresIdsMap.get(s.getSId())) && scoresIdsMap.get(s.getSId())
                            .size() >= 2) {
                        gradeCourse.setMaths(Double.valueOf(scoresIdsMap.get(s.getSId())
                                .get(1)
                                .getsScore()));
                    } else {
                        gradeCourse.setMaths(0.0);
                    }
                    if(ObjectUtil.isNotNull(scoresIdsMap.get(s.getSId())) && scoresIdsMap.get(s.getSId())
                            .size() >= 3) {
                        gradeCourse.setEnglish(Double.valueOf(scoresIdsMap.get(s.getSId())
                                .get(2)
                                .getsScore()));
                    } else {
                        gradeCourse.setEnglish(0.0);
                    }
                    return gradeCourse;
                })
                .toList();
    }

    /**
     * 19、按各科成绩进行排序，并显示排名
     */
    @Override
    public List<EachGrade> queryEachGrade() {

        Map<Integer,List<Course>> courses = courseMapper.selectList(null)
                .stream()
                .collect(Collectors.groupingBy(Course::getcId));


        Map<Integer,List<Score>> scores = scoreMapper.selectList(null)
                .stream()
                .collect(Collectors.groupingBy(Score::getsId));

        List<Student> students = studentMapper.selectList(null);

        return null;
    }


}
